USE dziennik;

DECLARE @LoginName sysname
DECLARE @SQL NVARCHAR(1000)

DECLARE DBLOGINS CURSOR FOR
    SELECT name FROM sys.database_principals
    WHERE name LIKE 'dziennik_%'

OPEN DBLOGINS

FETCH NEXT FROM DBLOGINS INTO @LoginName
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = 'DROP LOGIN [' + @LoginName + ']'
    EXEC sp_executesql @SQL
    FETCH NEXT FROM DBLOGINS INTO @LoginName
END

CLOSE DBLOGINS
DEALLOCATE DBLOGINS


use master;
go
alter database dziennik 
set single_user 
with rollback immediate;
go
drop database dziennik;
go



create database dziennik;
go
use dziennik;
go


create table uzytkownicy
(
	userId int identity not null,
	rodzaj int not null,

	primary key(userId),
	check (rodzaj>=1 and rodzaj<=4)
);
dbcc checkident('uzytkownicy', reseed, 10000);
go


create table nauczyciele
(
	userId int,
	imie nvarchar(15) not null,
	nazwisko nvarchar(60) not null,
	tytulNaukowy nvarchar(30) not null,
	pracuje bit not null default 1

	primary key(userId),
	foreign key  (userId)references uzytkownicy(userId)
);
go


create table jednostki
(
	id int identity not null,
	poczatek time not null,
	koniec time not null,

	primary key(id),
	check (datediff(minute, poczatek, koniec) = 45)
);
go


create table klasy 
(
	id 					int 		identity not null,
	semestr 			int 		not null,
	nazwa 				nvarchar(5) not null,
	userIdWychowawcy 	int 		not null,
	
	primary key(id),
	foreign key(userIdWychowawcy) references nauczyciele(userId),
	check(semestr>=1 and semestr<=12)
);
go


create table uczniowie 
(
	userId 				int				not null,
	pesel				bigint 			not null,
	imie 				nvarchar(15) 	not null,
	drugieImie 			nvarchar(15) 	default null,
	nazwisko 			nvarchar(60) 	not null,
	imieOjca 			nvarchar(15) 	default null,
	imieMatki 			nvarchar(15) 	default null,
	dataUrodzenia 		date 			not null,
	miejsceUrodzenia 	nvarchar(30) 	not null,
	idKlasy 			int				not null,

	
	primary key (userId),
	foreign key (idKlasy) references klasy(id),
	foreign key (userId) references uzytkownicy(userId)
);
go


create table opiekunowie
(
	userId		int				not null,
	imie 		nvarchar(15) 	not null,
	nazwisko 	nvarchar(60) 	not null,


	primary key (userId),
	foreign key (userId) references uzytkownicy(userId)
);
go



create table przypisaniOpiekunowie
(
	userIdOpiekuna 		int 	not null,
	userIdUcznia		int 	not null,

	primary key (userIdUcznia, userIdOpiekuna),
	foreign key (userIdOpiekuna)	references opiekunowie(userId),
	foreign key (userIdUcznia)	references uczniowie(userId)
);
go



create table przedmioty
(
	id					int 			identity not null,
	nazwa				nvarchar(35) 	not null,
	idKlasy				int				not null,
	userIdNauczyciela	int 			not null,
	semestr				smallint 		not null,
	rozsrzerzony		bit 			not null default 0,
	
	primary key (id),
	foreign key (idKlasy) references klasy(id),
	foreign key (userIdNauczyciela) references nauczyciele(userId),
	check(semestr>=1 and semestr<=12)
);
go



create table uwagi
(
	id 					int 			identity not null,
	userIdNauczyciela	int				not null,
	userIdUcznia 		int 			not null,
	tresc 				nvarchar(1024) 	not null,
	data 				date			not null,
	semestr				smallint		not null,
	
	primary key(id),
	foreign key(userIdUcznia) references uczniowie(userId),
	foreign key(userIdNauczyciela) references nauczyciele(userId)
);
go




create table oceny
(
	ocena 				float 			not null,
	waga				int				not null default 1,
	zaCo 				nvarchar(30)	not null,
	data 				date 			not null,
	userIdUcznia 		int 			not null,
	idPrzedmiotu 		int 			not null,
	
	foreign key(userIdUcznia) references uczniowie(userId),
	foreign key(idPrzedmiotu) references przedmioty(id),
	check(ocena>=1 and ocena<=6 and waga >=1)
);
go






create table ocenyKoncowe
(
	ocena 				int 			not null,
	data 				date 			not null,
	userIdUcznia 		int 			not null,
	idPrzedmiotu 		int 			not null,
	proponowana			bit				not null,
	
	
	foreign key(userIdUcznia) references uczniowie(userId),
	foreign key(idPrzedmiotu) references przedmioty(id),
	primary key(ocena, userIdUcznia, idPrzedmiotu, proponowana),
	check(ocena>=1 and ocena<=6)
);
go




create table zachowanie
(
	ocena 				int 			not null,
	data 				date 			not null,
	userIdUcznia 		int 			not null,
	idPrzedmiotu 	int 			not null,
	proponowana			bit				not null,

	foreign key(userIdUcznia) references uczniowie(userId),
	foreign key(idPrzedmiotu) references przedmioty(id),
	primary key(userIdUcznia, idPrzedmiotu, proponowana),
	check(ocena <=6 and ocena >=1)
);
go



create table zachowanieKoncowe
(	
	ocena 			int 		not null,
	data 			date 		not null,
	userIdUcznia 	int 		not null,
	semestr 		smallint 	not null,
	proponowana		bit			not null,

	primary key(userIdUcznia, semestr, proponowana),
	foreign key (userIdUcznia) references uczniowie(userId)	
);
go





create table wiadomosci
(
	id 				int 			identity not null,
	temat 			nvarchar(60)  	not null,
	tresc 			nvarchar(2000) 	not null,
	userIdNadawcy 	int 			not null,
	userIdOdbiorcy 	int 			not null,
	przeczytana		bit				not null default 0,
	czas			datetime		not null,

	primary key(id),
	foreign key(userIdNadawcy) references uzytkownicy(userId),
	foreign key(userIdOdbiorcy) references uzytkownicy(userId)
);
go




create table planlekcji
(
	nrSali	 		int 	not null,
	dzienTygodnia 	int 	not null,
	idJednostki		int 	not null,
	idPrzedmiotu 	int 	not null,	

	primary key(nrSali, dzienTygodnia, idJednostki),
	foreign key(idJednostki) references jednostki(id),
	foreign key(idPrzedmiotu) references przedmioty(id)
);
go